﻿#region << 版 本 注 释 >>
/*----------------------------------------------------------------
 * 修改人：x
 * 时间：
 * 修改说明：
 *----------------------------------------------------------------*/
#endregion << 版 本 注 释 >>using System;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using seejee_Backendium.Core.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
//using Dapper;

namespace seejee_Backendium.Data.Repositores
{
    public class ChartDataRepository
    {
        //private readonly IDbConnection _dbConnection;

        //public ChartDataRepository(IDbConnection dbConnection)
        //{
        //    _dbConnection = dbConnection;
        //}
        private readonly string _connectionString;
        private readonly IConfiguration _configuration;

        public ChartDataRepository(string connectionString, IConfiguration configuration)
        {
            _connectionString = connectionString;
            _configuration = configuration;
        }

        //public IEnumerable<BarData> GetDataByDate()
        //{

        //    //const string sql = "SELECT * FROM ValuesDemo";
        //    //return _dbConnection.Query<BarData>(sql, new { });
        //    string connStr = _configuration.GetConnectionString("Default");

        //    using (SqlConnection connection = new SqlConnection(connStr))
        //    {
        //        connection.Open();
        //        string query = "SELECT * FROM ValuesDemo";
        //        using (SqlCommand command = new SqlCommand(query, connection))
        //        {
        //            //command.Parameters.AddWithValue("@cCusName", cCusName);
        //            SqlDataAdapter adapter = new SqlDataAdapter(command);
        //            DataTable dataTable = new DataTable();
        //            adapter.Fill(dataTable);
        //            return dataTable;

        //        }
        //    }
        //}


        public async Task<string> GetConfigJsonAsync()
        {
            // 1. 获取连接字符串（从配置读取）
            string connStr = _configuration.GetConnectionString("Default"); // 从依赖注入的IConfiguration获取 [[2, 4, 14]]

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.OpenAsync(); // 异步打开连接

                // 2. 参数化SQL查询（防止SQL注入）[[20]]
                string query = "SELECT jsontext FROM ChartJson where id =1";

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // 3. 添加参数（类型安全）[[20]]
                    //command.Parameters.AddWithValue("@configKey", configKey);

                    // 4. 异步执行并处理结果
                    var result = await command.ExecuteScalarAsync();
                    Console.WriteLine(result);
                    // 5. 返回结果或默认值（与原逻辑一致）
                    return result?.ToString() ?? "{}"; // 返回非null值或空对象 [[18]]
                }
            }
        }

        public async Task<LineDataDTO> GetLineData(string cCusName, string cCusTableName, string xAxis, string yAxis)
        {
            // 1. 获取连接字符串（从配置读取）
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                await connection.OpenAsync(); // 异步打开连接
                // 2. 参数化SQL查询（防止SQL注入）
                string query = $"SELECT  {xAxis}, {yAxis}  FROM {cCusTableName} WHERE cCusName = @cCusName";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    //command.Parameters.AddWithValue("@cCusName", linedata.cCusName);
                    //command.Parameters.AddWithValue("@xAxies", xAxis);
                    //command.Parameters.AddWithValue("@yAxies", yAxis);
                    //command.Parameters.AddWithValue("@cCusTableName", cCusTableName);
                    command.Parameters.AddWithValue("@cCusName", cCusName);
                    // 3. 异步执行并处理结果
                    using (SqlDataReader reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            return new LineDataDTO
                            {
                                //cCusName = reader.GetString(0),
                                xAxis = reader[xAxis].ToString(),
                                yAxis = reader[yAxis].ToString()
                            };
                        }
                        else
                        {
                            return null; // 如果没有找到数据，返回null
                        }
                    }
                }
            }
        }
        public async Task<bool> CreateAsync(ChartJson entity)
        {
            string connStr = _configuration.GetConnectionString("Default");

            try
            {
                using (SqlConnection connection = new SqlConnection(connStr))
                {
                    await connection.OpenAsync();

                    string query = @"
                    INSERT INTO ChartJson (Jsontext, cCusName) 
                    VALUES (@JsonText, @CusName)";

                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        command.Parameters.Add("@JsonText", SqlDbType.VarChar, -1).Value = entity.JsonText;

                        // 处理可空字段
                        if (string.IsNullOrEmpty(entity.CusName))
                        {
                            command.Parameters.Add("@CusName", SqlDbType.VarChar, 100).Value = DBNull.Value;
                        }
                        else
                        {
                            command.Parameters.Add("@CusName", SqlDbType.VarChar, 100).Value = entity.CusName;
                        }

                        // 执行插入并检查受影响行数
                        int rowsAffected = await command.ExecuteNonQueryAsync();
                        return rowsAffected > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
        }

        //public async Task<string> RenderChartAsync(string sql)
        //{
        //    string connStr = _configuration.GetConnectionString("Default");
        //    try
        //    {
        //        using (SqlConnection connection = new SqlConnection(connStr))
        //        {
        //            await connection.OpenAsync();
        //            using (SqlCommand command = new SqlCommand(sql, connection))
        //            using (SqlDataReader reader = await command.ExecuteReaderAsync())
        //            {
        //                var result = new List<Dictionary<string, object>>();

        //                // 动态获取所有列名
        //                var columns = new List<string>();
        //                for (int i = 0; i < reader.FieldCount; i++)
        //                {
        //                    columns.Add(reader.GetName(i));
        //                }

        //                while (await reader.ReadAsync())
        //                {
        //                    var row = new Dictionary<string, object>();
        //                    foreach (var column in columns)
        //                    {
        //                        var ordinal = reader.GetOrdinal(column);
        //                        row[column] = reader.IsDBNull(ordinal) ? null : reader.GetValue(ordinal);
        //                    }
        //                    result.Add(row);
        //                }

        //                return JsonSerializer.Serialize(new
        //                {
        //                    success = true,
        //                    data = result,
        //                    count = result.Count,
        //                    columns = columns // 可选：返回列信息供前端使用
        //                });
        //            }
        //        }
        //    }


        //    catch (Exception ex)
        //    {
        //        return JsonSerializer.Serialize(new
        //        {
        //            success = false,
        //            error = new
        //            {
        //                message = ex.Message,
        //                type = ex.GetType().Name
        //            }
        //        });
        //    }
        //}
        //public async Task<string> RenderChartAsync(List<string> sqlList)
        //{
        //    string connStr = _configuration.GetConnectionString("Default");
        //    var result = new List<object>(); // 存储所有SQL查询结果

        //    try
        //    {
        //        using (SqlConnection connection = new SqlConnection(connStr))
        //        {
        //            await connection.OpenAsync();

        //            // 遍历所有SQL语句
        //            foreach (var sql in sqlList)
        //            {
        //                if (string.IsNullOrWhiteSpace(sql))
        //                {
        //                    // 空SQL处理
        //                    result.Add(new
        //                    {
        //                        success = false,
        //                        error = "Empty SQL statement"
        //                    });
        //                    continue;
        //                }

        //                using (SqlCommand command = new SqlCommand(sql, connection))
        //                using (SqlDataReader reader = await command.ExecuteReaderAsync())
        //                {
        //                    var dataSet = new List<Dictionary<string, object>>();

        //                    // 动态获取所有列名
        //                    var columns = new List<string>();
        //                    for (int i = 0; i < reader.FieldCount; i++)
        //                    {
        //                        columns.Add(reader.GetName(i));
        //                    }

        //                    // 读取数据
        //                    while (await reader.ReadAsync())
        //                    {
        //                        var row = new Dictionary<string, object>();
        //                        foreach (var column in columns)
        //                        {
        //                            var ordinal = reader.GetOrdinal(column);
        //                            row[column] = reader.IsDBNull(ordinal) ? null : reader.GetValue(ordinal);
        //                        }
        //                        dataSet.Add(row);
        //                    }

        //                    // 添加当前SQL查询结果
        //                    result.Add(new
        //                    {
        //                        success = true,
        //                        data = dataSet,
        //                        count = dataSet.Count,
        //                        columns = columns
        //                    });
        //                }
        //            }

        //            // 返回所有查询结果
        //            return JsonSerializer.Serialize(new
        //            {
        //                success = true,
        //                results = result
        //            });
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        // 返回错误信息
        //        return JsonSerializer.Serialize(new
        //        {
        //            success = false,
        //            error = new
        //            {
        //                message = ex.Message,
        //                type = ex.GetType().Name
        //            }
        //        });
        //    }
        //}
        //public async Task<string> RenderChartAsync(List<string> sqlList)
        //{
        //    string connStr = _configuration.GetConnectionString("Default");
        //    var result = new List<object>(); // 存储所有SQL查询结果

        //    try
        //    {
        //        using (SqlConnection connection = new SqlConnection(connStr))
        //        {
        //            await connection.OpenAsync();

        //            // 遍历所有SQL语句
        //            foreach (var sql in sqlList)
        //            {
        //                if (string.IsNullOrWhiteSpace(sql))
        //                {
        //                    // 空SQL处理
        //                    result.Add(new
        //                    {
        //                        success = false,
        //                        error = "Empty SQL statement"
        //                    });
        //                    continue;
        //                }

        //                using (SqlCommand command = new SqlCommand(sql, connection))
        //                {
        //                    // 处理不同类型的结果
        //                    if (sql.Trim().ToUpper().StartsWith("SELECT"))
        //                    {
        //                        // SELECT查询 - 返回数据集
        //                        using (SqlDataReader reader = await command.ExecuteReaderAsync())
        //                        {
        //                            var dataSet = new List<Dictionary<string, object>>();

        //                            // 动态获取所有列名
        //                            var columns = new List<string>();
        //                            for (int i = 0; i < reader.FieldCount; i++)
        //                            {
        //                                columns.Add(reader.GetName(i));
        //                            }

        //                            // 读取数据
        //                            while (await reader.ReadAsync())
        //                            {
        //                                var row = new Dictionary<string, object>();
        //                                foreach (var column in columns)
        //                                {
        //                                    var ordinal = reader.GetOrdinal(column);
        //                                    row[column] = reader.IsDBNull(ordinal) ? null : reader.GetValue(ordinal);
        //                                }
        //                                dataSet.Add(row);
        //                            }

        //                            // 添加当前SQL查询结果
        //                            result.Add(new
        //                            {
        //                                success = true,
        //                                type = "dataset",
        //                                data = dataSet,
        //                                count = dataSet.Count,
        //                                columns = columns
        //                            });
        //                        }
        //                    }
        //                    else
        //                    {
        //                        // 非SELECT查询 - 返回标量值或影响行数
        //                        object scalarResult = await command.ExecuteScalarAsync();

        //                        // 处理聚合函数结果（如SUM, COUNT, AVG等）
        //                        if (scalarResult != null)
        //                        {
        //                            result.Add(new
        //                            {
        //                                success = true,
        //                                type = "scalar",
        //                                value = scalarResult
        //                            });
        //                        }
        //                        else
        //                        {
        //                            // 没有返回值的操作（如INSERT, UPDATE, DELETE）
        //                            int rowsAffected = await command.ExecuteNonQueryAsync();
        //                            result.Add(new
        //                            {
        //                                success = true,
        //                                type = "operation",
        //                                rowsAffected = rowsAffected
        //                            });
        //                        }
        //                    }
        //                }
        //            }

        //            // 返回所有查询结果
        //            return JsonSerializer.Serialize(new
        //            {
        //                success = true,
        //                results = result
        //            });
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        // 返回详细的错误信息
        //        return JsonSerializer.Serialize(new
        //        {
        //            success = false,
        //            error = new
        //            {
        //                message = ex.Message,
        //                type = ex.GetType().Name,
        //                stackTrace = ex.StackTrace
        //            }
        //        });
        //    }
        //}
        public async Task<string> RenderChartAsync(List<string> sqlList)
        {
            string connStr = _configuration.GetConnectionString("Default");
            var result = new List<object>();

            try
            {
                using (SqlConnection connection = new SqlConnection(connStr))
                {
                    await connection.OpenAsync();

                    foreach (var sql in sqlList)
                    {
                        if (string.IsNullOrWhiteSpace(sql))
                        {
                            result.Add(new
                            {
                                success = false,
                                error = "空SQL语句"
                            });
                            continue;
                        }

                        // 统一处理所有查询类型
                        using (SqlCommand command = new SqlCommand(sql, connection))
                        {
                            // 先尝试执行查询（适用于SELECT）
                            if (sql.Trim().ToUpper().StartsWith("SELECT"))
                            {
                                using (SqlDataReader reader = await command.ExecuteReaderAsync())
                                {
                                    var dataSet = new List<Dictionary<string, object>>();
                                    var columns = new List<string>();

                                    // 读取列信息
                                    for (int i = 0; i < reader.FieldCount; i++)
                                    {
                                        columns.Add(reader.GetName(i));
                                    }

                                    // 读取数据
                                    while (await reader.ReadAsync())
                                    {
                                        var row = new Dictionary<string, object>();
                                        foreach (var column in columns)
                                        {
                                            var ordinal = reader.GetOrdinal(column);
                                            row[column] = reader.IsDBNull(ordinal) ? null : reader.GetValue(ordinal);
                                        }
                                        dataSet.Add(row);
                                    }

                                    // 确定结果类型
                                    string resultType = "dataset";
                                    if (columns.Count == 1 && dataSet.Count == 1)
                                    {
                                        resultType = "scalar";
                                    }

                                    result.Add(new
                                    {
                                        success = true,
                                        type = resultType,
                                        data = dataSet,
                                        columns = columns,
                                        count = dataSet.Count
                                    });
                                }
                            }
                            else
                            {
                                // 处理非查询语句
                                int rowsAffected = await command.ExecuteNonQueryAsync();
                                result.Add(new
                                {
                                    success = true,
                                    type = "operation",
                                    rowsAffected = rowsAffected
                                });
                            }
                        }
                    }

                    // 返回统一格式
                    return JsonSerializer.Serialize(new
                    {
                        success = true,
                        results = result
                    });
                }
            }
            catch (Exception ex)
            {
                // 详细错误信息
                return JsonSerializer.Serialize(new
                {
                    success = false,
                    error = new
                    {
                        message = ex.Message,
                        type = ex.GetType().Name,
                        stackTrace = ex.StackTrace
                    }
                });
            }
        }
    }
}
